package com.psm.util;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;
import java.util.jar.Attributes;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.jfinal.plugin.activerecord.Db;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.*;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

/**
 * Created by cheam on 15/7/30.
 */
public class PoiExt {
    public static Map<String, Object> ReadExcel(String filename, String sql, int ColCount) {
        Map<String, Object> map = null;
        try {
            @SuppressWarnings("resource")
            HSSFWorkbook wookbook = new HSSFWorkbook(new FileInputStream(filename));
            HSSFSheet sheet = wookbook.getSheet("Sheet1");
            int rows = sheet.getPhysicalNumberOfRows();
            Object[][] paras = new Object[rows-1][ColCount];
            for (int i = 1; i < rows; i++) {
                HSSFRow row = sheet.getRow(i);
                if (row != null) {
                    int cells = row.getPhysicalNumberOfCells();
//                    for (int j = 0; j < cells; j++) {
                    for (int j = 0; j < ColCount; j++) {
                        HSSFCell cell = row.getCell(j);
                        if (cell != null) {
                            switch (cell.getCellType()) {
                                case HSSFCell.CELL_TYPE_FORMULA:
                                    break;
                                case HSSFCell.CELL_TYPE_NUMERIC:
                                    paras[i-1][j] = cell.getNumericCellValue();
                                    paras[i-1][j] = paras[i-1][j].toString().replace(".0","");

                                    break;
                                case HSSFCell.CELL_TYPE_STRING:
                                    paras[i-1][j] = cell.getStringCellValue();
                                    break;
                                default:
                                    paras[i-1][j] = null;
                                    break;
                            }
                        }
                    }
                }
            }
            int[] ret = Db.batch(sql, paras, 100);
            int s = 0, l = 0;
            for (int i = 0; i < ret.length; i++)
                if (ret[i] == 1)
                    s++;
                else
                    l++;
            map = new HashMap<String, Object>();
            map.put("success", s);
            map.put("lost", l);
            map.put("count", ret.length);
            wookbook = null;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return map;
    }

    public static Map<String, Object> ReadExcel(String filename,String Sheet1, String sql, int ColCount) {

        Map<String, Object> map = null;
        try {
            @SuppressWarnings("resource")
            String hz = filename.substring(filename.lastIndexOf("."),filename.length());

            if(hz.equals(".xls"))//针对2003版本
            {
                HSSFWorkbook wookbook = new HSSFWorkbook(new FileInputStream(filename));
                HSSFSheet sheet = wookbook.getSheetAt(0);

                int rows = sheet.getPhysicalNumberOfRows();
                Object[][] paras = new Object[rows-1][ColCount];
                for (int i = 1; i < rows; i++) {
                    HSSFRow row = sheet.getRow(i);
                    if (row != null) {
                        int cells = row.getPhysicalNumberOfCells();
                        for (int j = 0; j < ColCount; j++) {
                            HSSFCell cell = row.getCell(j);
                            if (cell != null) {
                                switch (cell.getCellType()) {
                                    case HSSFCell.CELL_TYPE_FORMULA:
                                        break;
                                    case HSSFCell.CELL_TYPE_NUMERIC:
                                        paras[i-1][j] = cell.getNumericCellValue();
                                        paras[i-1][j] = paras[i-1][j].toString().replace(".0","");
                                        break;
                                    case HSSFCell.CELL_TYPE_STRING:
                                        paras[i-1][j] = cell.getStringCellValue();
                                        break;
                                    default:
                                        paras[i-1][j] = null;
                                        break;
                                }
                            }
                        }
                    }
                }
                int[] ret = Db.batch(sql, paras, 100);
                int s = 0, l = 0;
                for (int i = 0; i < ret.length; i++)
                    if (ret[i] == 1)
                        s++;
                    else
                        l++;
                map = new HashMap<String, Object>();
                map.put("success", s);
                map.put("lost", l);
                map.put("count", ret.length);
                wookbook = null;

            } else { //针对2007+

                InputStream is = new FileInputStream(filename);
                XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
                XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
                if(xssfSheet != null){
                    int rows = xssfSheet.getPhysicalNumberOfRows();
                    Object[][] paras = new Object[rows-1][ColCount];

                    for (int i = 1; i < rows; i++) {
                        XSSFRow row = xssfSheet.getRow(i);
                        if (row != null) {
                            for (int j = 0; j < ColCount; j++) {
                                XSSFCell cell = row.getCell(j);
                                if (cell != null) {
                                    switch (cell.getCellType()) {
                                        case HSSFCell.CELL_TYPE_FORMULA:
                                            break;
                                        case HSSFCell.CELL_TYPE_NUMERIC:
                                            paras[i-1][j] = cell.getNumericCellValue();
                                            paras[i-1][j] = paras[i-1][j].toString().replace(".0","");
                                            break;
                                        case HSSFCell.CELL_TYPE_STRING:
                                            paras[i-1][j] = cell.getStringCellValue();
                                            break;
                                        default:
                                            paras[i-1][j] = null;
                                            break;
                                    }
                                }
                            }
                        }
                    }

                    int[] ret = Db.batch(sql, paras, 100);
                    int s = 0, l = 0;
                    for (int i = 0; i < ret.length; i++)
                        if (ret[i] == 1)
                            s++;
                        else
                            l++;
                    map = new HashMap<String, Object>();
                    map.put("success", s);
                    map.put("lost", l);
                    map.put("count", ret.length);

                }else{
                    map = new HashMap<String, Object>();
                }

            }

        } catch (IOException e) {
            e.printStackTrace();
        }
        return map;
    }

    public static XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
        XMLReader parser =
                XMLReaderFactory.createXMLReader(
                        "org.apache.xerces.parsers.SAXParser"
                );
        ContentHandler handler = new SheetHandler(sst);
        parser.setContentHandler(handler);
        return parser;
    }

    /**
     * See org.xml.sax.helpers.DefaultHandler javadocs
     */
    private static class SheetHandler extends DefaultHandler {
        private SharedStringsTable sst;
        private String lastContents;
        private boolean nextIsString;

        private SheetHandler(SharedStringsTable sst) {
            this.sst = sst;
        }

        public void startElement(String uri, String localName, String name,
                                 Attributes attributes) throws SAXException {
            // c => cell
            if(name.equals("c")) {
                // Print the cell reference
                System.out.print(attributes.getValue("r") + " - ");
                // Figure out if the value is an index in the SST
                String cellType = attributes.getValue("t");
                if(cellType != null && cellType.equals("s")) {
                    nextIsString = true;
                } else {
                    nextIsString = false;
                }
            }
            // Clear contents cache
            lastContents = "";
        }

        public void endElement(String uri, String localName, String name)
                throws SAXException {
            // Process the last contents as required.
            // Do now, as characters() may be called more than once
            if(nextIsString) {
                int idx = Integer.parseInt(lastContents);
                lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
                nextIsString = false;
            }

            // v => contents of a cell
            // Output after we've seen the string contents
            if(name.equals("v")) {
                System.out.println(lastContents);
            }
        }

        public void characters(char[] ch, int start, int length)
                throws SAXException {
            lastContents += new String(ch, start, length);
        }
    }
}
